﻿
using AutoMapper;
using GPS.DataAccessLayer;
using GPS.ENTITYS;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.UI.WebControls;
using PagedList;
using GPS.COMMON;
using System.IO;
using System.Web.UI;
using System.Data.Entity.Infrastructure;
namespace GPS.Controllers.GPS_BaoCao
{
    public class GPS_CB_GiayPhepCNKDController : Controller
    {
        DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;
        //
        // GET: /GPS_HTCapGPLX/
        #region Index
        public ActionResult Index(ViewReportModelGPS_CANHBAO_GPCNKD SearchModel, int? currentPage)
        {
            GridView gv = new GridView();
            var param2 = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (SearchModel.pDVGTVT_MA == null ? DBNull.Value : (object)SearchModel.pDVGTVT_MA), ParameterDirection.Input);
            var param3 = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var entities = _db.DbContext.Database.SqlQuery<RepostModelGPS_CANHBAO_GPCNKD>("BEGIN GPS_CANHBAO.GPS_CANHBAO_PTGCNKD(:pDVGTVT_MA, :pRET); end;", param2, param3).ToList();
            initialCategorySearchAction();
            ViewBag.TotalRow = entities.Count();
            var model = new List<RepostModelGPS_CANHBAO_GPCNKD>();
            foreach (var currentEntity in entities)
            {
                model.Add(Mapper.Map<RepostModelGPS_CANHBAO_GPCNKD>(currentEntity));
            }
            var pageIndex = SearchModel.Page ?? 1;
            SearchModel.SearchResults = model.ToPagedList(pageIndex, GPS.COMMON.Constants.PageSize);
            return View(SearchModel);
        }

        [HttpPost]
        public void initialCategorySearchAction()
        {
            ViewBag.lstDVGTVT = new SelectList(_db.Filter<SDONVIGTVT>(x=>x.DVGTVT_MAQL == "40"), "DVGTVT_MA", "DVGTVT_TEN");
            //using (var connection = _db.DbContext.Database.Connection)
            //{
            //    connection.Open();
            //    var command = connection.CreateCommand();
            //    command.CommandText = "EXEC [dbo].[Pets]";

            //    using (var reader = command.ExecuteReader())
            //    {
            //        var cats =
            //            ((IObjectContextAdapter)_db)
            //                .ObjectContext
            //                .Translate<Cat>(reader)
            //                .ToList();

            //        reader.NextResult();

            //        var dogs =
            //            ((IObjectContextAdapter)_db)
            //                .ObjectContext
            //                .Translate<Dog>(reader)
            //                .ToList();
            //    }
            //}
        }


        public ActionResult ExportToExcel(ViewReportModelGPS_CANHBAO_GPCNKD SearchModel)
        {
            GridView gv = new GridView();
            var param2 = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (SearchModel.pDVGTVT_MA == null ? DBNull.Value : (object)SearchModel.pDVGTVT_MA), ParameterDirection.Input);
            var param3 = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var entities = _db.DbContext.Database.SqlQuery<RepostModelGPS_CANHBAO_GPCNKD>("BEGIN GPS_CANHBAO.GPS_CANHBAO_PTGCNKD(:pDVGTVT_MA, :pRET); end;", param2, param3).ToList();
            initialCategorySearchAction();
            DataTable tbl = GPS.COMMON.Helpers.MapListToDataTable<RepostModelGPS_CANHBAO_GPCNKD>(entities);
            gv.DataSource = tbl;
            
            gv.DataBind();
            Response.ClearContent();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment; filename=CanhBaoGiayChungNhanKiemDinh.xls");
            Response.ContentType = "application/ms-excel";
            Response.Charset = "";
            StringWriter sw = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);
            gv.RenderControl(htw);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();
            return RedirectToAction("Index");
        }
        #endregion
	}
}